package com.boss.salary.dao.impl;

import com.boss.common.util.DateUtils;
import com.boss.framework.dao.BaseDaoImpl;
import com.boss.framework.dto.PaginationDTO;
import com.boss.salary.constant.SystemDictConstant;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.ISalaryMonthEndDao;
import com.boss.salary.entity.AgencyInfo;
import com.boss.salary.entity.SalaryBasedata;
import com.boss.salary.entity.SalaryMonthEnd;
import com.boss.salary.vo.SalaryMonthEndVO;
import com.boss.salary.vo.UnitSalaryReportVO;
import com.boss.sys.utils.SysUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

@Repository
public class SalaryMonthEndDaoImpl extends BaseDaoImpl implements ISalaryMonthEndDao {

    private static DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    private static final int NO_THIRTEEN = 0; //不是十三月工资

    @Override
    public void insertSalaryMonthEndDao(SalaryMonthEnd data) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        LocalDateTime localDateTime = LocalDateTime.now();
        List<Object> params = new ArrayList<Object>() {{
            add(SysUtils.generateGuid());
            add(localDateTime.format(dtf));
            add(localDateTime.format(dtf));
            add(data.getAgencyId());
            add(data.getPersonId());
            add(NO_THIRTEEN);
            add(data.getMofDivCode());
        }};
        StringBuilder sb = new StringBuilder();
        sb.append(" insert into ")
                .append(TableName.SALARY_MONTH_END)
                .append(" (id, create_time, update_time, agency_id, person_id, is_thirteen,mof_div_code ");

        String columnNames = data.getColumnNames();
        if (!StringUtils.isEmpty(columnNames)) {
            String[] columnNameArr = columnNames.split(",");
            List<String> colunmNameList = Arrays.stream(columnNameArr)
                    .map(name -> name.split("_")[0])
                    .collect(Collectors.toList());
            for (String columnName : colunmNameList) {
                sb.append(" , ").append(columnName);
            }
        }
        sb.append(" ) ").append(" values").append("(?,?,?,?,?,?,?");

        if (!StringUtils.isEmpty(columnNames)) {
            String[] columnNameArr = columnNames.split(",");
            List<String> colunmNameList = Arrays.stream(columnNameArr)
                    .map(name -> name.split("_")[0])
                    .collect(Collectors.toList());
            for (String columnName : colunmNameList) {
                sb.append(",?");
                Class<?> clazz = data.getClass();
                Method getMethod = clazz.getDeclaredMethod("get" +
                        columnName.substring(0, 1).toUpperCase() + columnName.substring(1));
                params.add(getMethod.invoke(data));
            }
        }
        sb.append(" ) ");
        super.getjdbcTemplate().update(sb.toString(), params.toArray());
    }

    @Override
    public void updateSalaryMonthEndDao(SalaryMonthEnd data) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        LocalDateTime localDateTime = LocalDateTime.now();
        List<Object> params = new ArrayList<Object>() {{
            add(localDateTime.format(dtf));
            add(data.getAgencyId());
           // add(data.getPersonId());
        }};
        StringBuilder sb = new StringBuilder();
        sb.append(" update ")
                .append(TableName.SALARY_MONTH_END)
                .append(" set ")
                .append(" update_time = ?, agency_id = ? ");
        String columnNames = data.getColumnNames();
        if (!StringUtils.isEmpty(columnNames)) {
            String[] columnNameArr = columnNames.split(",");
            List<String> colunmNameList = Arrays.stream(columnNameArr)
                    .map(name -> name.split("_")[0])
                    .collect(Collectors.toList());
            for (String columnName : colunmNameList) {
                sb.append(" ,").append(columnName).append(" = ? ");
                Class<?> clazz = data.getClass();
                Method getMethod = clazz.getDeclaredMethod("get" +
                        columnName.substring(0, 1).toUpperCase() + columnName.substring(1));
                params.add(getMethod.invoke(data));
            }
        }
        sb.append(" where ");
        if (!StringUtils.isEmpty(data.getId())) {
            sb.append("id = ? ");
            params.add(data.getId());
        } else if (!StringUtils.isEmpty(data.getPersonId())) {
            sb.append(" person_id = ? ");
            params.add(data.getPersonId());
        }
        super.getjdbcTemplate().update(sb.toString(), params.toArray());
    }

    @Override
    public UnitSalaryReportVO selectReallySendSalarySum(String agencyId, Integer queryThirteen, String reallySendSalaryName, String salaryType) {
        List<Object> params = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                //如果sum(reallySendSalaryName)为空则把0.00赋值给 sum
                .append(" nvl(sum(").append(reallySendSalaryName).append("), 0.00) reallySendSalarySum, ")
                .append(" nvl(sum(case pio.state when '3' then ").append(reallySendSalaryName)
                .append(" else 0 end ), 0.00) workSalarySum, ")
                .append(" nvl(sum(case pio.state when '1' then ").append(reallySendSalaryName)
                .append(" else 0 end ), 0.00) retireSalarySum ")
                .append(" from ")
                .append(TableName.PERSON_INFO).append(" pio ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pio.id = pet.person_id and pet.is_deleted = 0 ")
                .append(" and pet.is_stop_salary = 0 and pet.agency_id = ? ");
        params.add(agencyId);
        sb.append(" left join ").append(TableName.SALARY_MONTH_END).append(" smed ")
                .append(" on ").append(" pio.id = smed.person_id and smed.agency_id = ? ");
        params.add(agencyId);
        if(SystemDictConstant.IS_FALSE.equals(queryThirteen)){
            sb.append(" and smed.is_thirteen = ? ");
            params.add(queryThirteen);
        }
        sb.append(" where ").append(" pio.is_deleted = 0 ");
        if (!StringUtils.isEmpty(salaryType)) {
            sb.append(" and pet.merit_pay_code = ?");
            params.add(salaryType);
        }
        return super.getjdbcTemplate().queryForObject(sb.toString(), new BeanPropertyRowMapper<>(UnitSalaryReportVO.class), params.toArray());
    }

    @Override
    public UnitSalaryReportVO selectSubmitReallySendSalarySum(String agencyId, String reallySendSalaryName, String salaryType) {
        List<Object> params = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                //如果sum(reallySendSalaryName)为空则把0.00赋值给 sum
                .append(" nvl(sum(").append(reallySendSalaryName).append("), 0.00) reallySendSalarySum, ")
                .append(" nvl(sum(case pet.state when '3' then ").append(reallySendSalaryName)
                .append(" else 0 end ), 0.00) workSalarySum, ")
                .append(" nvl(sum(case pet.state when '1' then ").append(reallySendSalaryName)
                .append(" else 0 end ), 0.00) retireSalarySum ")
                .append(" from ")
                .append(TableName.PERSON_EXT_SUBMIT).append(" pet ")
                .append(" join ").append(TableName.SALARY_MONTH_END_SUBMIT).append(" smed ")
                .append(" on ").append(" pet.person_id = smed.person_id  and pet.agency_id=smed.agency_id ")
                .append(" where  pet.is_stop_salary = 0")
                .append(" and pet.agency_id = ?  ");
                params.add(agencyId);
//        if(SystemDictConstant.IS_FALSE.equals(queryThirteen)){
//            sb.append(" and smed.is_thirteen = ? ");
//            params.add(queryThirteen);
//        }
        if (!StringUtils.isEmpty(salaryType)) {
            sb.append(" and pet.merit_pay_code = ?");
            params.add(salaryType);
        }
        return super.getjdbcTemplate().queryForObject(sb.toString(), new BeanPropertyRowMapper<>(UnitSalaryReportVO.class), params.toArray());
    }

    @Override
    public Double selectSubmitReallySendChangeSum(String agencyId, String reallySendSalaryName){
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" nvl(sum(").append(reallySendSalaryName).append("), 0.00) reallySendSalarySum ")
                .append(" from ")
                .append(TableName.SALARY_MONTH_BEGIN)
                .append(" where agency_id = ? ");
        return this.getJdbcTemplate().queryForObject(sql.toString(),Double.class,agencyId);

    }

    @Override
    public String selectSubmitSalarySum(Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" sum(").append(queryParam.get("salaryCloumnName")).append(") ")
                .append(" from ")
                .append(TableName.SALARY_MONTH_END).append(" a")
                .append(" join ").append(TableName.AGENCY_VERIFY).append(" b")
                .append(" on a.agency_id = b.agency_id")
                .append(" join ").append(TableName.PERSON_INFO).append(" pio")
                .append(" on a.person_id = pio.id")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet")
                .append(" on pio.id = pet.person_id")
                .append(" where ")
                .append(" a.agency_id = '").append(queryParam.get("agencyId")).append("'")
                .append(" and pet.is_stop_salary = 0 ")
                .append(" and b.is_month_end = 0 and b.is_thirteen = 0  and b.salary_verified_in_agency_code = 1");
        if (!StringUtils.isEmpty(queryParam)) {
            if (!StringUtils.isEmpty(queryParam.get("flag"))) {
                if ("0".equals(queryParam.get("flag"))) {
                    // 未上报和撤销
                    sql.append(" and (b.is_salary_submit = '0' or b.is_salary_submit = '-9')");
                }
            }
            if (!StringUtils.isEmpty(queryParam.get("personType"))) {
                sql.append(" and pio.state = ? ");
                paramList.add(queryParam.get("personType"));
            }
            if (!StringUtils.isEmpty(queryParam.get("personName"))) {
                sql.append(" and pio.person_name like concat( '%', ?, '%') ");
                paramList.add(queryParam.get("personName"));
            }
            if (!StringUtils.isEmpty(queryParam.get("idenNo"))) {
                sql.append(" and pio.iden_no like concat( '%', ?, '%') ");
                paramList.add(queryParam.get("idenNo"));
            }
        }
        return super.getjdbcTemplate().queryForObject(sql.toString(), String.class, paramList.toArray());
    }


    @Override
    public List<SalaryMonthEnd> getSalaryMonthEndByAgencyIds(String agencyId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select * from ").append(TableName.SALARY_MONTH_END_SUBMIT)//修改该处查询为上报数据
//                .append(" where ").append(com.boss.sys.utils.SysUtils.inSql("agency_id", agencyIds));
                .append(" where agency_id=? ");
        return this.getjdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(SalaryMonthEnd.class),agencyId);
    }

    @Override
    public void updateAgencyIdByPersonId(String personId, String agencyId, String updateTime) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.SALARY_MONTH_END).append(" set ")
                .append("agency_id = ?, update_time = ?");
        sql.append(" where person_id = ?");
        this.getjdbcTemplate().update(sql.toString(), new Object[]{agencyId, updateTime, personId});
    }

    @Override
    public List<Map<String, Object>> selectPersonSalaryInfoPage(PaginationDTO paginationDTO,
                                                                String agencyId,
                                                                List<String> salaryIds,
                                                                String salaryMonth,
                                                                Map<String, String> currentSalaryIdJoinColumnMap,
                                                                Map<String, Object> queryForm) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder ifNullCurrentStr = new StringBuilder();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append("rownum \"index\", id \"id\",updateTime \"updateTime\",personId \"personId\",agencyId \"agencyId\",currentMonth \"currentMonth\", ");
        if (!CollectionUtils.isEmpty(salaryIds)) {
            StringBuilder currentStr = new StringBuilder();
            ifNullCurrentStr = new StringBuilder();
            for (String salaryId : salaryIds) {
                String currentColumn = currentSalaryIdJoinColumnMap.get(salaryId);
                currentStr.append("\"").append(currentColumn).append("\" , ");
                ifNullCurrentStr.append(" nvl( smed.").append(currentColumn).append(", 0 ) ").append(" \" ").append(currentColumn).append(" \" , ");
            }
            sb.append(currentStr).append(" deptId \"deptId\",idenNo \"idenNo\",personName \"personName\" ");
        }
        sb.append(" from ( ");
        sb.append(" select ")
                .append(" smed.id, smed.update_time updateTime, pio.id personId, pet.agency_id agencyId, ")
                .append(" ? currentMonth, ");
        paramList.add(salaryMonth);
        if (!CollectionUtils.isEmpty(salaryIds)) {
            sb.append(ifNullCurrentStr);
        }
        sb.append(" pet.dept_id deptId, pio.iden_no idenNo, pio.person_name personName ");
        sb.append(" from ")
                .append(TableName.PERSON_INFO).append(" pio ")
                .append(" left join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pio.id = pet.person_id and pet.is_deleted = 0 ")
                .append(" left join ").append(TableName.SALARY_MONTH_END).append(" smed ")
                .append(" on ").append(" pio.id = smed.person_id ")
                .append(" where ")
                .append(" pio.is_deleted = 0 ") // 过滤掉已删除的用户
                .append(" and pet.is_stop_salary = ? ") // 过滤掉已停发的用户
                .append(" and pet.agency_id = ? ");

        paramList.add(SystemDictConstant.IS_FALSE);
        paramList.add(agencyId);
        if(!StringUtils.isEmpty(queryForm.get("salaCardBank"))){
             sb.append(" and pet.sala_card_bank = ?");
             paramList.add(queryForm.get("salaCardBank"));
        }

        sb.append(" ) t, (select @i:=0) n order by updateTime desc ");
        String querySql = super.boxUpSql(sb.toString());
        //请求参数初始化
        Object[] params = super.boxUpParam(paramList.toArray(), paginationDTO);
        return super.getJdbcTemplate().queryForList(querySql, params);
    }

    @Override
    public int selectPersonSalaryInfoTotal(String agencyId, Map<String, Object> condition) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" count(*) ")
                .append(" from ")
                .append(TableName.PERSON_INFO).append(" pio ")
                .append(" left join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pio.id = pet.person_id and pet.is_deleted = 0 ")
                .append(" where ")
                .append(" pio.is_deleted = 0 and pet.is_stop_salary = ? and pet.agency_id = ? ");
        paramList.add(SystemDictConstant.IS_FALSE);
        paramList.add(agencyId);
        if(!StringUtils.isEmpty(condition.get("salaCardBank"))){
            sb.append(" and pet.sala_card_bank=?");
            paramList.add(condition.get("salaCardBank"));
        }
        return super.getJdbcTemplate().queryForObject(sb.toString(), paramList.toArray(), int.class);
    }

    @Override
    public List<Map<String, Object>> selectThirteenSalaryPage(String agencyId,
                                                              String salaryMonth,
                                                              List<String> salaryColumns) {
        StringBuilder sb = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sb.append(" select ")
                .append(" \"index\", stmh.id \"id\", ")
                .append(" ? \"salaryMonth\", ");
        paramList.add(salaryMonth);
        sb.append(" pet.dept_id \"deptId\", stmh.agency_id \"agencyId\", pio.id \"personId\", pio.iden_no \"idenNo\", pio.person_name \"personName\" ");
        for (String column : salaryColumns) {
            sb.append(" , nvl(").append(column).append(", 0) ").append(" \" ").append(column).append(" \" ");
        }
        sb.append(" from ")
                .append(" (select @i:=0) t, ")
                .append(TableName.PERSON_INFO).append(" pio ")
                .append(" join ")
                .append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pet.person_id = pio.id and pet.is_deleted = 0 ")
                .append(" and pet.is_stop_salary = 0 and pet.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" join ")
                .append(TableName.SALARY_THIRTEEN_MONTH).append(" stmh ")
                .append(" on ").append(" stmh.person_id = pio.id and stmh.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" where ").append(" pio.is_deleted = 0 ");
        return super.getjdbcTemplate().queryForList(sb.toString(), paramList.toArray());
    }

    @Override
    public Map<String, Object> selectThirteenSalarySum(String agencyId,
                                                             List<String> salaryColumns) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select ");
        for (String column : salaryColumns) {
            sb.append(" nvl(sum(").append(column).append("), 0) ").append(column);
            if(salaryColumns.indexOf(column) != salaryColumns.size() - 1){
                sb.append(" , ");
            }
        }
        sb.append(" from ")
                .append(TableName.PERSON_INFO).append(" pio ")
                .append(" join ")
                .append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pet.person_id = pio.id and pet.is_deleted = 0 ")
                .append(" and pet.is_stop_salary = 0 and pet.agency_id = ? ")
                .append(" join ")
                .append(TableName.SALARY_THIRTEEN_MONTH).append(" stmh ")
                .append(" on ").append(" stmh.person_id = pio.id and stmh.is_thirteen = 1 and stmh.agency_id = ? ")
                .append(" where ").append(" pio.is_deleted = 0 ");
        return super.getjdbcTemplate().queryForMap(sb.toString(), agencyId, agencyId);
    }

    @Override
    public void physicalDeleteThirteenMonthSalary(String agencyId) {
        StringBuilder sb = new StringBuilder();
        sb.append(" delete from ")
                .append(TableName.SALARY_THIRTEEN_MONTH)
                .append(" where ")
                .append(" agency_id = ? ");
        super.getjdbcTemplate().update(sb.toString(), agencyId);
    }

    @Override
    public void batchUpdateThirteenMonthSalary(String[] columns,
                                               List<Object[]> list) {
        StringBuilder sb = new StringBuilder();
        sb.append(" update ")
                .append(TableName.SALARY_THIRTEEN_MONTH)
                .append(" set ")
                .append(" update_time = ? ");
        for(String column : columns){
            sb.append(" , ").append(column).append(" = ? ");
        }
        sb.append(" where ").append(" id = ? ");
        this.getJdbcTemplate().batchUpdate(sb.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return list.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = list.get(i);
                for(int a = 0; a < 3; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void addPersonSalary(Object[] params) {
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into ").append(TableName.SALARY_MONTH_END);
        sql.append(" (id, create_time, update_time, agency_id, person_id, is_thirteen,mof_div_code) ");
        sql.append(" values (?, ?, ?, ?, ?, ?,?)");
        this.getjdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public void deletePersonSalary(String personId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" delete from ").append(TableName.SALARY_MONTH_END);
        sql.append(" where person_id = ?");
        this.getjdbcTemplate().update(sql.toString(), personId);
    }

    @Override
    public void updatePersonSalary(String personId,String agencyId) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.SALARY_MONTH_END)
           .append(" set agency_id= ? where person_id = ?");
        Object[] params =new Object[]{
                agencyId,personId
        };
        this.getjdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public void updateThirteenSalaryByPersonAndAgencyId(String personId, String agencyId, Double thirteenSalary) {
        StringBuilder sb = new StringBuilder();
        List<Object> params = new ArrayList<>();
        sb.append(" update ").append(TableName.SALARY_MONTH_END)
                .append(" set ").append(" update_time = ?, ");
        params.add(String.valueOf(DateUtils.getCurrentTimeStamp()));
        sb.append(" s100 = ?, s1 = s1 + s100, s2 = s2 + s100 ");
        params.add(thirteenSalary);
        sb.append(" where ").append(" person_id = ? and agency_id = ? ");
        params.add(personId);
        params.add(agencyId);
        super.getjdbcTemplate().update(sb.toString(), params.toArray());
    }

    @Override
    public void clearThirteenSalaryByPersonAndAgencyId(String personId, String agencyId) {
        StringBuilder sb = new StringBuilder();
        List<Object> params = new ArrayList<>();
        sb.append(" update ").append(TableName.SALARY_MONTH_END)
                .append(" set ").append(" update_time = ?, ");
        params.add(String.valueOf(DateUtils.getCurrentTimeStamp()));
        sb.append(" s1 = s1 - s100, s2 = s2 - s100 ");
        sb.append(" where ").append(" person_id = ? and agency_id = ? ");
        params.add(personId);
        params.add(agencyId);
        super.getjdbcTemplate().update(sb.toString(), params.toArray());
    }

    @Override
    public void clearThirteenSalaryByAgencyId(String agencyId) {
        StringBuilder sb = new StringBuilder();
        List<Object> params = new ArrayList<>();
        sb.append(" update ").append(TableName.SALARY_MONTH_END)
                .append(" set ").append(" update_time = ?, ");
        params.add(String.valueOf(DateUtils.getCurrentTimeStamp()));
        sb.append(" s1 = s1 - s100, s2 = s2 - s100, s100 = 0 ");
        sb.append(" where ").append(" agency_id = ? ");
        params.add(agencyId);
        super.getjdbcTemplate().update(sb.toString(), params.toArray());
    }

    @Override
    public int selectCountByPersonAndAgencyId(String personId, String agencyId) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select  count(*) from ").append(TableName.SALARY_MONTH_END)
                .append(" where ").append(" agency_id = ? and person_id = ? ");
        return super.getjdbcTemplate().queryForObject(sb.toString(), int.class, agencyId, personId);
    }



    @Override
    public void clearSalaryMonthEndByColum(String colum, AgencyInfo agencyInfo){

        StringBuilder sb = new StringBuilder();
        sb.append(" update ")
                .append(TableName.SALARY_MONTH_END)
                .append(" set ")
                .append(colum)
                .append("=0");

        if(null!=agencyInfo){
            sb.append(" where agency_id=?");
            Object[] params = new Object[]{
                    agencyInfo.getId()
            };
            super.getjdbcTemplate().update(sb.toString(),params);
        }else {
            super.getjdbcTemplate().update(sb.toString());
        }
    }

    @Override
    public void updateRealAndWages(SalaryBasedata salaryBasedata, AgencyInfo agencyInfo) {
        StringBuilder sb = new StringBuilder();
        sb.append(" update ")
                .append(TableName.SALARY_MONTH_END)
                .append(" set s1=s1");
        if(salaryBasedata.getIsAccsalPay().equals(SystemDictConstant.ENABLE)||
                salaryBasedata.getIsAccsalWithhold().equals(SystemDictConstant.ENABLE)){
            sb.append("+")
              .append(salaryBasedata.getSalaryCode()).append(",s2=s2+").append(salaryBasedata.getSalaryCode());
        }else{
            sb.append("-")
                    .append(salaryBasedata.getSalaryCode()).append(",s2=s2-").append(salaryBasedata.getSalaryCode());
        }
        if(null!=agencyInfo){
            sb.append(" where agency_id=?");
            Object[] params = new Object[]{
                    salaryBasedata.getId()
            };
            super.getjdbcTemplate().update(sb.toString(),params);
        }else {
            super.getjdbcTemplate().update(sb.toString());
        }
    }

    @Override
    public void updateSalaryMonthEndByAgencyIdAndPersonId(SalaryMonthEndVO data)throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
            Class<?> clazz = SalaryMonthEndVO.class;
            List<Object> params = new ArrayList<>();
            params.add(String.valueOf(DateUtils.getCurrentTimeStamp()));
            for(int i = 1; i < 101; i++){
                Method getMethod = clazz.getDeclaredMethod("getS"+i);
                params.add(getMethod.invoke(data));
            }
                params.add(data.getId());
            StringBuilder sb = new StringBuilder();
            sb.append(" update ").append(TableName.SALARY_MONTH_END)
                    .append(" set ").append(" update_time = ? ");
            for(int i = 1; i < 101; i++){
                sb.append(" ,s").append(i).append(" = ? ");
            }
            sb.append(" where ").append(" id = ? ");
        super.getjdbcTemplate().update(sb.toString(), params.toArray());
    }

    @Override
    public void batchUpdateSalaryMonthEnd(List<SalaryMonthEndVO> salaryMonthEndVOs) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        if(!CollectionUtils.isEmpty(salaryMonthEndVOs)){
            List<Object[]> paramList = new ArrayList<>(salaryMonthEndVOs.size());
            Class<?> clazz = SalaryMonthEndVO.class;
            for(SalaryMonthEndVO salaryMonthEndVO : salaryMonthEndVOs){
                List<Object> params = new ArrayList<>();
                params.add(String.valueOf(DateUtils.getCurrentTimeStamp()));
                for(int i = 1; i < 101; i++){
                    Method getMethod = clazz.getDeclaredMethod("getS"+i);
                    params.add(getMethod.invoke(salaryMonthEndVO));
                }
//                params.add(salaryMonthEndVO.getAgencyId());
                params.add(salaryMonthEndVO.getId());
                paramList.add(params.toArray());
            }
            StringBuilder sb = new StringBuilder();
            sb.append(" update ").append(TableName.SALARY_MONTH_END)
                    .append(" set ").append(" update_time = ? ");
            for(int i = 1; i < 101; i++){
                sb.append(" ,s").append(i).append(" = ? ");
            }
            sb.append(" where ").append(" id = ? ");
            super.getjdbcTemplate().batchUpdate(sb.toString(), new BatchPreparedStatementSetter() {
                public int getBatchSize() {
                    return paramList.size();
                    //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
                }
                public void setValues(PreparedStatement ps, int i)throws SQLException {
                    Object[] sme = paramList.get(i);
                    for(int a = 0; a < sme.length; a++){
                        ps.setObject(a+1,sme[a]);
                    }

                }
            });
        }
    }

    @Override
    public Integer getPaidWagesCount(String agencyId, String zeroAble,List<String> computeColumns) {
        StringBuilder sb = new StringBuilder();
        String s2=zeroAble.equals("1")?" nvl(ssme.s2,0) < 0 ":" nvl(ssme.s2,0) <= 0 ";
        StringBuffer notNegative=new StringBuffer();
        if(!CollectionUtils.isEmpty(computeColumns)) {
            for (String column : computeColumns) {
                if(!"s2".equals(column)) {
                    notNegative.append("nvl(ssme.").append(column).append(",0)<0  or ");
                }
            }
        }
        sb.append(" SELECT COUNT(*) FROM ").append(TableName.PERSON_INFO)
            .append("  spi left JOIN ").append(TableName.SALARY_MONTH_END)
            .append("  ssme on ssme.person_id = spi.id where ssme.agency_id = ? " )
            .append(" and spi.is_deleted = 0 and (nvl(ssme.s1,0)<0  or ")
            .append(notNegative)
            .append(s2)
            .append(" )");
        return super.getjdbcTemplate().queryForObject(sb.toString(), int.class, agencyId);
    }

    @Override
    public List<String> selectDifferentPersonId(String agencyId) {
        StringBuilder sb = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sb.append(" select id from ( ")
                .append(" select ").append(" pio.id ");
        StringBuilder whereStr = new StringBuilder(" s2 = 0 ");
        for(int i = 1; i <= 100; i++){
            sb.append(" ,nvl(smed.s").append(i).append(", 0) s").append(i);
            sb.append(" ,nvl(smbn.s").append(i).append(", 0) s").append(i).append("_history ");
            whereStr.append(" or s").append(i).append(" != ").append("s").append(i).append("_history ");
        }
        sb.append(" from ").append(TableName.PERSON_INFO).append(" pio ")
                .append(" join ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" on ").append(" pio.id = pet.person_id and pet.is_deleted = 0 and pet.agency_id = ? ");
        paramList.add(agencyId);
        sb.append("  join ").append(TableName.SALARY_MONTH_END).append(" smed ")
                .append(" on ").append(" pio.id = smed.person_id and smed.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" left  join ").append(TableName.SALARY_MONTH_BEGIN).append(" smbn ")
                .append(" on ").append(" pio.id = smbn.person_id and smbn.agency_id = ? ");
        paramList.add(agencyId);
        sb.append(" where ").append(" pio.is_deleted = 0 ");
        sb.append(" ) t ").append(" where ").append(whereStr.toString());
        return super.getjdbcTemplate().queryForList(sb.toString(), paramList.toArray(), String.class);
    }

    @Override
    public void updateRealAndWagesAndSub(String agencyId,List<String> addlist,List<String> sublist) {
        StringBuilder sb = new StringBuilder();
        sb.append(" update ")
                .append(TableName.SALARY_MONTH_END)
                .append(" set s1=");
        StringBuilder addStr= new StringBuilder();
        StringBuilder subStr= new StringBuilder();
        addStr.append("0");
        for(String add:addlist){
            addStr.append("+")
                    .append("nvl(")
                    .append(add)
                    .append(",0)");
        }
        subStr.append("0");
        for(String sub:sublist){
            subStr.append("-")
                    .append("nvl(")
                    .append(sub)
                    .append(",0)");
        }
        sb.append(addStr)
                .append("-")
                .append(subStr);
        sb.append(",s2=")
                .append(addStr);

        if(null!=agencyId){
            sb.append(" where agency_id=?");
            Object[] params = new Object[]{
                    agencyId
            };
            super.getjdbcTemplate().update(sb.toString(),params);
        }else {
            super.getjdbcTemplate().update(sb.toString());
        }
    }

    @Override
    public List<SalaryMonthEndVO> selectSalaryMonthEndWithSalaryType(String province, List<String> agencyIds) {
        StringBuilder sb = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sb.append(" select ")
                .append(" smed.id,pet.agency_id, pet.person_id, pet.merit_pay_code, pet.mof_div_code,is_thirteen, ")
                .append(" s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17, s18, s19, s20, ")
                .append(" s21, s22, s23, s24, s25, s26, s27, s28, s29, s30, s31, s32, s33, s34, s35, s36, s37, s38, s39, s40, ")
                .append(" s41, s42, s43, s44, s45, s46, s47, s48, s49, s50, s51, s52, s53, s54, s55, s56, s57, s58, s59, s60, ")
                .append(" s61, s62, s63, s64, s65, s66, s67, s68, s69, s70, s71, s72, s73, s74, s75, s76, s77, s78, s79, s80, ")
                .append(" s81, s82, s83, s84, s85, s86, s87, s88, s89, s90, s91, s92, s93, s94, s95, s96, s97, s98, s99, s100 ")
                .append(" from ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" join ").append(TableName.SALARY_MONTH_END).append(" smed ")
                .append(" on ").append(" pet.person_id = smed.person_id ")
                .append(" where ").append(" pet.is_deleted = 0 and pet.is_stop_salary=0 and smed.mof_div_code = ? and pet.mof_div_code = ? ");
        paramList.add(province);
        paramList.add(province);
        if(!CollectionUtils.isEmpty(agencyIds)){
            sb.append(" and ").append(SysUtils.inSql("smed.agency_id", agencyIds));
        }
        try{
            return super.getJdbcTemplate().query(sb.toString(), paramList.toArray(), new BeanPropertyRowMapper<>(SalaryMonthEndVO.class));
        }catch(EmptyResultDataAccessException e){
            return Collections.emptyList();
        }
    }
    @Override
    public List<SalaryMonthEndVO> selectSalaryMonthEndWithSalaryTypes(String province, String agencyIds) {
        StringBuilder sb = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sb.append(" select ")
                .append(" smed.id,pet.agency_id, pet.person_id, pet.merit_pay_code, pet.mof_div_code,is_thirteen, ")
                .append(" s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17, s18, s19, s20, ")
                .append(" s21, s22, s23, s24, s25, s26, s27, s28, s29, s30, s31, s32, s33, s34, s35, s36, s37, s38, s39, s40, ")
                .append(" s41, s42, s43, s44, s45, s46, s47, s48, s49, s50, s51, s52, s53, s54, s55, s56, s57, s58, s59, s60, ")
                .append(" s61, s62, s63, s64, s65, s66, s67, s68, s69, s70, s71, s72, s73, s74, s75, s76, s77, s78, s79, s80, ")
                .append(" s81, s82, s83, s84, s85, s86, s87, s88, s89, s90, s91, s92, s93, s94, s95, s96, s97, s98, s99, s100 ")
                .append(" from ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" join ").append(TableName.SALARY_MONTH_END).append(" smed ")
                .append(" on ").append(" pet.person_id = smed.person_id ")
                .append(" where ").append(" pet.is_deleted = 0 and smed.mof_div_code = ? ");
        paramList.add(province);
        if(!"null".equals(agencyIds)&&null!=agencyIds){
            sb.append(" and  smed.agency_id = ?");
            paramList.add(agencyIds);
        }
        try{
            return super.getJdbcTemplate().query(sb.toString(), paramList.toArray(), new BeanPropertyRowMapper<>(SalaryMonthEndVO.class));
        }catch(EmptyResultDataAccessException e){
            return  Collections.emptyList();
        }
    }

    @Override
    public List<SalaryMonthEndVO> selectSalaryMonthEndSubmitWithSalaryType(String province, List<String> agencyIds) {
        StringBuilder sb = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sb.append(" select ")
                .append(" pet.agency_id, pet.person_id, pet.merit_pay_code, pet.mof_div_code, ")
                .append(" s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17, s18, s19, s20, ")
                .append(" s21, s22, s23, s24, s25, s26, s27, s28, s29, s30, s31, s32, s33, s34, s35, s36, s37, s38, s39, s40, ")
                .append(" s41, s42, s43, s44, s45, s46, s47, s48, s49, s50, s51, s52, s53, s54, s55, s56, s57, s58, s59, s60, ")
                .append(" s61, s62, s63, s64, s65, s66, s67, s68, s69, s70, s71, s72, s73, s74, s75, s76, s77, s78, s79, s80, ")
                .append(" s81, s82, s83, s84, s85, s86, s87, s88, s89, s90, s91, s92, s93, s94, s95, s96, s97, s98, s99, s100 ")
                .append(" from ").append(TableName.PERSON_EXT_SUBMIT).append(" pet ")
                .append(" join ").append(TableName.SALARY_MONTH_END_SUBMIT).append(" smed ")
                .append(" on ").append(" pet.person_id = smed.person_id ")
                .append(" where ").append(" pet.is_deleted = 0 and smed.mof_div_code = ? ");
        paramList.add(province);
        if(!CollectionUtils.isEmpty(agencyIds)){
            sb.append(" and ").append(SysUtils.inSql("smed.agency_id", agencyIds));
        }
        try{
            return super.getJdbcTemplate().query(sb.toString(), paramList.toArray(), new BeanPropertyRowMapper<>(SalaryMonthEndVO.class));
        }catch(EmptyResultDataAccessException e){
            return Collections.emptyList();
        }
    }
    @Override
    public List<SalaryMonthEndVO> selectSalaryMonthEndSubmitWithSalaryTypes(String province,String agencyId) {
        StringBuilder sb = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sb.append(" select ")
                .append(" pet.agency_id, pet.person_id, pet.merit_pay_code, pet.mof_div_code, ")
                .append(" s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17, s18, s19, s20, ")
                .append(" s21, s22, s23, s24, s25, s26, s27, s28, s29, s30, s31, s32, s33, s34, s35, s36, s37, s38, s39, s40, ")
                .append(" s41, s42, s43, s44, s45, s46, s47, s48, s49, s50, s51, s52, s53, s54, s55, s56, s57, s58, s59, s60, ")
                .append(" s61, s62, s63, s64, s65, s66, s67, s68, s69, s70, s71, s72, s73, s74, s75, s76, s77, s78, s79, s80, ")
                .append(" s81, s82, s83, s84, s85, s86, s87, s88, s89, s90, s91, s92, s93, s94, s95, s96, s97, s98, s99, s100 ")
                .append(" from ").append(TableName.PERSON_EXT_SUBMIT).append(" pet ")
                .append(" join ").append(TableName.SALARY_MONTH_END_SUBMIT).append(" smed ")
                .append(" on ").append(" pet.person_id = smed.person_id ")
                .append(" where ").append(" pet.is_deleted = 0 and smed.mof_div_code = ? ");
        paramList.add(province);
        if (null != agencyId && !"null".equals(agencyId)) {
            sb.append(" and smed.agency_id=?");
            paramList.add(agencyId);
        }
        try {
            return super.getJdbcTemplate().query(sb.toString(), new BeanPropertyRowMapper<>(SalaryMonthEndVO.class), paramList.toArray());
        } catch (EmptyResultDataAccessException e) {
            return  Collections.emptyList();
        }
    }


            @Override
    public List<SalaryMonthEndVO> querySalaryByPersonIdOrMofDivCode(String mofDivCode, List<String> personIds) {
        StringBuilder sb = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sb.append(" select ")
                .append(" pet.agency_id, pet.person_id, pet.merit_pay_code, pet.mof_div_code, ")
                .append(" s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17, s18, s19, s20, ")
                .append(" s21, s22, s23, s24, s25, s26, s27, s28, s29, s30, s31, s32, s33, s34, s35, s36, s37, s38, s39, s40, ")
                .append(" s41, s42, s43, s44, s45, s46, s47, s48, s49, s50, s51, s52, s53, s54, s55, s56, s57, s58, s59, s60, ")
                .append(" s61, s62, s63, s64, s65, s66, s67, s68, s69, s70, s71, s72, s73, s74, s75, s76, s77, s78, s79, s80, ")
                .append(" s81, s82, s83, s84, s85, s86, s87, s88, s89, s90, s91, s92, s93, s94, s95, s96, s97, s98, s99, s100 ")
                .append(" from ").append(TableName.PERSON_EXT).append(" pet ")
                .append(" join ").append(TableName.SALARY_MONTH_END).append(" smed ")
                .append(" on ").append(" pet.person_id = smed.person_id ")
                .append(" where ").append(" pet.is_deleted = 0 and smed.mof_div_code = ? ");
        paramList.add(mofDivCode);
        if(!CollectionUtils.isEmpty(personIds)){
            sb.append(" and ").append(SysUtils.inSql("smed.person_id", personIds));
        }
        try{
            return super.getJdbcTemplate().query(sb.toString(), paramList.toArray(), new BeanPropertyRowMapper<>(SalaryMonthEndVO.class));
        }catch(EmptyResultDataAccessException e){
            return Collections.emptyList();
        }
    }

    @Override
    public List<Map<String, Object>> selectMXOffer(String mof_div_code, List<String> columnList) {
        StringBuilder sb = new StringBuilder();
        List<Object> params = new ArrayList<>();
        sb.append(" select ")
                .append(" pet.sala_card_bank \"salaCardBank\", ")
                .append(" avy.agency_code \"agencyCode\", ")
                .append(" avy.agency_name \"agencyName\", ")
                .append(" case when pet.state = '3' then '在职'else '离退休' end \"state\", ")
                .append(" case when pet.merit_pay_code is null or pet.merit_pay_code = '' then '0' else pet.merit_pay_code end  \"meritPayCode\", ")
                .append(" case when pet.sala_gr_code is null or pet.sala_gr_code = '' then '0' else sala_gr_code end  \"salaGrCode\", ")
                .append(" decode(smed.is_thirteen,1,'2021-13',avy.salary_month)||pet.iden_no \"personCode\", ")
                .append(" pet.sala_card_no \"salaCardNo\", ")
                .append(" pet.person_name \"personName\", ")
                .append(" pet.iden_no \"idenNo\" ");
        if(!CollectionUtils.isEmpty(columnList)){
            for(String column : columnList){
                if(!"0".equals(column)){
                    sb.append(" , case when smed.").append(column).append(" is null ")
                            .append(" or smed.").append(column).append(" = 0 then  0  else smed.").append(column)
                            .append(" end \"").append(column).append("\" ");
                }
            }
        }
        sb.append(" from ").append(TableName.PERSON_EXT_SUBMIT).append(" pet ")
                .append(" join ").append(TableName.SALARY_MONTH_END_SUBMIT).append(" smed ")
                .append(" on ").append(" smed.person_id = pet.person_id and smed.agency_id=pet.agency_id ")
                .append(" join ").append(TableName.AGENCY_VERIFY).append(" avy ")
                .append(" on ").append(" avy.agency_id = pet.agency_id and avy.is_deleted = 0 ")
                .append(" and avy.is_bank_data_created = 0 and avy.is_month_end = 0 and is_pay_data_created = 1")
                .append(" where pet.mof_div_code=? ");
        params.add(mof_div_code);
        return super.getjdbcTemplate().queryForList(sb.toString(), params.toArray());
    }

    /**
     * 通过点位id 查询这个单位下的所有人的工资项
     * @param agencyId
     * @return
     */
    @Override
    public List<SalaryMonthEndVO> getSalaryMonthEndVOByPersonId(String agencyId) {
        StringBuilder sb = new StringBuilder();
        sb.append("select  a.id,a.create_time,a.update_time,a.agency_id,a.person_id,a.mof_div_code,b.merit_pay_code,b.iden_no,")
                .append("a.s1, a.s2, a.s3, a.s4, a.s5, a.s6, a.s7, a.s8, a.s9, a.s10, a.s11, a.s12, a.s13, a.s14, a.s15, a.s16, a.s17, a.s18, a.s19, a.s20, ")
                .append("a.s21, a.s22, a.s23, a.s24, a.s25, a.s26, a.s27, a.s28, a.s29, a.s30, a.s31, a.s32, a.s33, a.s34, a.s35, a.s36, a.s37, a.s38, a.s39, a.s40, ")
                .append("a.s41, a.s42, a.s43, a.s44, a.s45, a.s46, a.s47, a.s48, a.s49, a.s50, a.s51, a.s52, a.s53, a.s54, a.s55, a.s56, a.s57, a.s58, a.s59, a.s60, ")
                .append("a.s61, a.s62, a.s63, a.s64, a.s65, a.s66, a.s67, a.s68, a.s69, a.s70, a.s71, a.s72, a.s73, a.s74, a.s75, a.s76, a.s77, a.s78, a.s79, a.s80, ")
                .append("a.s81, a.s82, a.s83, a.s84, a.s85, a.s86, a.s87, a.s88, a.s89, a.s90, a.s91, a.s92, a.s93, a.s94, a.s95, a.s96, a.s97, a.s98, a.s99, a.s100  from ")
                .append(TableName.SALARY_MONTH_END).append(" a ")
                .append(" join ").append(TableName.PERSON_EXT).append(" b ")
                .append(" on a.person_id=b.person_id ")
                .append(" where ")
                .append("a.agency_id=?");
        Object[] params= new Object[]{
                agencyId
        };
        return this.getjdbcTemplate().query(sb.toString(),new BeanPropertyRowMapper(SalaryMonthEndVO.class),params);
    }

    @Override
        public void batchSaveSalaryMonthEnd(List<SalaryMonthEndVO> salaryMonthEndVOList) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        if(salaryMonthEndVOList.size()>0){
        List<Object[]> paramList = new ArrayList<>(salaryMonthEndVOList.size()-1);
        Class<?> clazz = SalaryMonthEndVO.class;
        for(SalaryMonthEndVO salaryMonthEndVO : salaryMonthEndVOList){
            Object[] paramArr = {
                    SysUtils.generateGuid(), String.valueOf(DateUtils.getCurrentTimeStamp()), String.valueOf(DateUtils.getCurrentTimeStamp()),
                    salaryMonthEndVO.getAgencyId(), salaryMonthEndVO.getPersonId(), salaryMonthEndVO.getIsThirteen(),salaryMonthEndVO.getMofDivCode()
            };
            List<Object> params = new ArrayList<>(Arrays.asList(paramArr));
            for(int i = 1; i < 101; i++){
                Method getMethod = clazz.getDeclaredMethod("getS"+i);
                params.add(getMethod.invoke(salaryMonthEndVO));
            }
            paramList.add(params.toArray());
        }
        StringBuilder sb = new StringBuilder();


            sb.append(" merge into ").append(TableName.SALARY_MONTH_END)
                    .append(" a using " )
                    .append(" (select ? id, ? create_time, ? update_time, ? agency_id, ? person_id, ? is_thirteen,? mof_div_code, ? ")
                    .append(" s1, ? s2, ? s3, ? s4, ? s5, ? s6, ? s7, ? s8, ? s9, ? s10, ? s11, ? s12, ? s13, ? s14, ? s15, ? s16, ? s17, ? s18, ? s19, ? s20, ?  ")
                    .append(" s21, ? s22, ? s23, ? s24, ? s25, ? s26, ? s27, ? s28, ? s29, ? s30, ? s31, ? s32, ? s33, ? s34, ? s35, ? s36, ? s37, ? s38, ? s39, ? s40, ? ")
                    .append(" s41, ? s42, ? s43, ? s44, ? s45, ? s46, ? s47, ? s48, ? s49, ? s50, ? s51, ? s52, ? s53, ? s54, ? s55, ? s56, ? s57, ? s58, ? s59, ? s60, ? ")
                    .append(" s61, ? s62, ? s63, ? s64, ? s65, ? s66, ? s67, ? s68, ? s69, ? s70, ? s71, ? s72, ? s73, ? s74, ? s75, ? s76, ? s77, ? s78, ? s79, ? s80, ? ")
                    .append(" s81, ? s82, ? s83, ? s84, ? s85, ? s86, ? s87, ? s88, ? s89, ? s90, ? s91, ? s92, ? s93, ? s94, ? s95, ? s96, ? s97, ? s98, ? s99, ? s100, ? ")
                    .append(" s101, ? s102, ? s103, ? s104, ? s105, ? s106, ? s107, ? s108, ? s109, ? s110, ? s111, ? s112, ? s113, ? s114, ? s115, ? s116, ? s117, ? s118, ? s119, ? s120, ? ")
                    .append(" s121, ? s122, ? s123, ? s124, ? s125, ? s126, ? s127, ? s128, ? s129, ? s130, ? s131, ? s132, ? s133, ? s134, ? s135, ? s136, ? s137, ? s138, ? s139, ? s140, ? ")
                    .append(" s141, ? s142, ? s143, ? s144, ? s145, ? s146, ? s147, ? s148, ? s149, ? s150 from dual ) b ")
                    .append(" on (a.person_id = b.person_id) " )
                    .append(" WHEN MATCHED THEN " )
                    .append("  UPDATE SET  a.create_time=b.create_time, a.update_time=b.update_time, a.agency_id=b.agency_id, a.is_thirteen=b.is_thirteen, a.mof_div_code=b.mof_div_code, " )
                    .append(" a.s1=b.s1, a.s2=b.s2, a.s3=b.s3,a.s4=b.s4,a.s5=b.s5,a.s6=b.s6, a.s7=b.s7,a.s8=b.s8, a.s9=b.s9,a.s10=b.s10, a.s11=b.s11, a.s12=b.s12,a.s13=b.s13, a.s14=b.s14,a.s15=b.s15, a.s16=b.s16, a.s17=b.s17,a.s18=b.s18, a.s19=b.s19,a.s20=b.s20, ")
                    .append(" a.s21=b.s21, a.s22=b.s22, a.s23=b.s23,a.s24=b.s24, a.s25=b.s25,a.s26=b.s26, a.s27=b.s27, a.s28=b.s28,a.s29=b.s29, a.s30=b.s30,a.s31=b.s31, a.s32=b.s32, a.s33=b.s33,a.s34=b.s34, a.s35=b.s35,a.s36=b.s36, a.s37=b.s37, a.s38=b.s38,a.s39=b.s39, a.s40=b.s40, ")
                    .append(" a.s41=b.s41, a.s42=b.s42, a.s43=b.s43,a.s44=b.s44, a.s45=b.s45,a.s46=b.s46, a.s47=b.s47, a.s48=b.s48,a.s49=b.s49, a.s50=b.s50,a.s51=b.s51, a.s52=b.s52, a.s53=b.s53,a.s54=b.s54, a.s55=b.s55,a.s56=b.s56, a.s57=b.s57, a.s58=b.s58,a.s59=b.s59, a.s60=b.s60,  ")
                    .append(" a.s61=b.s61, a.s62=b.s62, a.s63=b.s63,a.s64=b.s64, a.s65=b.s65,a.s66=b.s66, a.s67=b.s67, a.s68=b.s68,a.s69=b.s69, a.s70=b.s70,a.s71=b.s71, a.s72=b.s72, a.s73=b.s73,a.s74=b.s74, a.s75=b.s75,a.s76=b.s76, a.s77=b.s77, a.s78=b.s78,a.s79=b.s79, a.s80=b.s80,  ")
                    .append(" a.s81=b.s81, a.s82=b.s82, a.s83=b.s83,a.s84=b.s84, a.s85=b.s85,a.s86=b.s86, a.s87=b.s87, a.s88=b.s88,a.s89=b.s89, a.s90=b.s90,a.s91=b.s91, a.s92=b.s92, a.s93=b.s93,a.s94=b.s94, a.s95=b.s95,a.s96=b.s96, a.s97=b.s97, a.s98=b.s98,a.s99=b.s99, a.s100=b.s100,  ")
                    .append(" a.s101=b.s101, a.s102=b.s102, a.s103=b.s103,a.s104=b.s104, a.s105=b.s105,a.s106=b.s106, a.s107=b.s107, a.s108=b.s108,a.s109=b.s109, a.s110=b.s110,a.s111=b.s111, a.s112=b.s112, a.s113=b.s113,a.s114=b.s114,a.s115=b.s115, a.s116=b.s116,a.s117=b.s117,a.s118=b.s118, a.s119=b.s119, a.s120=b.s120,  ")
                    .append(" a.s121=b.s121, a.s122=b.s122, a.s123=b.s123,a.s124=b.s124, a.s125=b.s125,a.s126=b.s126, a.s127=b.s127, a.s128=b.s128,a.s129=b.s129, a.s130=b.s130,a.s131=b.s131, a.s132=b.s132, a.s133=b.s133,a.s134=b.s134, a.s135=b.s135,a.s136=b.s136, a.s137=b.s137, a.s138=b.s138,a.s139=b.s139, a.s140=b.s140,  ")
                    .append(" a.s141=b.s141, a.s142=b.s142,a.s143=b.s143, a.s144=b.s144,a.s145=b.s145, a.s146=b.s146, a.s147=b.s147,a.s148=b.s148, a.s149=b.s149,a.s150=b.s150 ")
                    .append( " WHEN NOT MATCHED THEN " )
                    .append( "   INSERT " )
                    .append(" ( a.id, a.create_time, a.update_time, a.agency_id, a.person_id, a.is_thirteen,a.mof_div_code, ")
                    .append(" a.s1, a.s2, a.s3, a.s4, a.s5, a.s6, a.s7, a.s8, a.s9, a.s10, a.s11, a.s12, a.s13, a.s14, a.s15, a.s16, a.s17, a.s18, a.s19, a.s20,  ")
                    .append(" a.s21, a.s22, a.s23, a.s24, a.s25, a.s26, a.s27, a.s28, a.s29, a.s30, a.s31, a.s32, a.s33, a.s34, a.s35, a.s36, a.s37, a.s38, a.s39, a.s40, ")
                    .append(" a.s41, a.s42, a.s43, a.s44, a.s45, a.s46, a.s47, a.s48, a.s49, a.s50, a.s51, a.s52, a.s53, a.s54, a.s55, a.s56, a.s57, a.s58, a.s59, a.s60, ")
                    .append(" a.s61, a.s62, a.s63, a.s64, a.s65, a.s66, a.s67, a.s68, a.s69, a.s70, a.s71, a.s72, a.s73, a.s74, a.s75, a.s76, a.s77, a.s78, a.s79, a.s80, ")
                    .append(" a.s81, a.s82, a.s83, a.s84, a.s85, a.s86, a.s87, a.s88, a.s89, a.s90, a.s91, a.s92, a.s93, a.s94, a.s95, a.s96, a.s97, a.s98, a.s99, a.s100, ")
                    .append( "   VALUES " )
                    .append(" ( b.id, b.create_time, b.update_time, b.agency_id, b.person_id, b.is_thirteen,b.mof_div_code, ")
                    .append(" b.s1, b.s2, b.s3, b.s4, b.s5, b.s6, b.s7, b.s8, b.s9, b.s10, b.s11, b.s12, b.s13, b.s14, b.s15, b.s16, b.s17, b.s18, b.s19, b.s20,  ")
                    .append(" b.s21, b.s22, b.s23, b.s24, b.s25, b.s26, b.s27, b.s28, b.s29, b.s30, b.s31, b.s32, b.s33, b.s34, b.s35, b.s36, b.s37, b.s38, b.s39, b.s40, ")
                    .append(" b.s41, b.s42, b.s43, b.s44, b.s45, b.s46, b.s47, b.s48, b.s49, b.s50, b.s51, b.s52, b.s53, b.s54, b.s55, b.s56, b.s57, b.s58, b.s59, b.s60, ")
                    .append(" b.s61, b.s62, b.s63, b.s64, b.s65, b.s66, b.s67, b.s68, b.s69, b.s70, b.s71, b.s72, b.s73, b.s74, b.s75, b.s76, b.s77, b.s78, b.s79, b.s80, ")
                    .append(" b.s81, b.s82, b.s83, b.s84, b.s85, b.s86, b.s87, b.s88, b.s89, b.s90, b.s91, b.s92, b.s93, b.s94, b.s95, b.s96, b.s97, b.s98, b.s99, b.s100, ");

        super.getJdbcTemplate().batchUpdate(sb.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramList.get(i);
                for(int a = 0; a < 107; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
        }
    }

    @Override
    public Double selectSubmitReallyShouldSendSum(String agencyId, String shouldSend) {
            StringBuilder sql = new StringBuilder();
            sql.append(" select ")
                    .append(" nvl(sum(").append(shouldSend).append("), 0.00) reallyShouldSendSum ")
                    .append(" from ")
                    .append(TableName.SALARY_MONTH_BEGIN)
                    .append(" where agency_id = ? ");
            return this.getJdbcTemplate().queryForObject(sql.toString(),Double.class,agencyId);
    }

    @Override
    public String getZeroAble(String province) {
        try{
            StringBuilder sql = new StringBuilder();
            sql.append(" select ")
               .append(" is_able ")
               .append(" from ")
               .append(TableName.SAL_CONFIG_CTRL)
               .append(" where config_type='zeroAble' and  mof_div_code = ? ");
            return this.getJdbcTemplate().queryForObject(sql.toString(),String.class,province);
        }catch (Exception e){
            return "1";
        }
    }
    @Override
    public List<SalaryMonthEndVO> getSalaryMonthEndVOByPerson(String province) {
        StringBuilder sb = new StringBuilder();
        sb.append("select  a.id,a.create_time,a.update_time,a.agency_id,a.person_id,a.mof_div_code,b.merit_pay_code,b.iden_no,")
                .append("a.s1, a.s2, a.s3, a.s4, a.s5, a.s6, a.s7, a.s8, a.s9, a.s10, a.s11, a.s12, a.s13, a.s14, a.s15, a.s16, a.s17, a.s18, a.s19, a.s20, ")
                .append("a.s21, a.s22, a.s23, a.s24, a.s25, a.s26, a.s27, a.s28, a.s29, a.s30, a.s31, a.s32, a.s33, a.s34, a.s35, a.s36, a.s37, a.s38, a.s39, a.s40, ")
                .append("a.s41, a.s42, a.s43, a.s44, a.s45, a.s46, a.s47, a.s48, a.s49, a.s50, a.s51, a.s52, a.s53, a.s54, a.s55, a.s56, a.s57, a.s58, a.s59, a.s60, ")
                .append("a.s61, a.s62, a.s63, a.s64, a.s65, a.s66, a.s67, a.s68, a.s69, a.s70, a.s71, a.s72, a.s73, a.s74, a.s75, a.s76, a.s77, a.s78, a.s79, a.s80, ")
                .append("a.s81, a.s82, a.s83, a.s84, a.s85, a.s86, a.s87, a.s88, a.s89, a.s90, a.s91, a.s92, a.s93, a.s94, a.s95, a.s96, a.s97, a.s98, a.s99, a.s100  from ")
                .append(TableName.SALARY_MONTH_END).append(" a ")
                .append(" join ").append(TableName.PERSON_EXT).append(" b ")
                .append(" on a.person_id=b.person_id ")
                .append(" where ")
                .append("a.mof_div_code=?");
        Object[] params= new Object[]{
                province
        };
        return this.getjdbcTemplate().query(sb.toString(),new BeanPropertyRowMapper(SalaryMonthEndVO.class),params);
    }
}

